﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class frm_Add_ExportQuotation : Form
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        public frm_Add_ExportQuotation()
        {            
            InitializeComponent();
            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            LoadCustomer();
            this.customerID.Properties.AutoComplete = true;
            LoadStaff();
            this.requestedBy.Properties.AutoComplete = true;
            this.madeBy.Properties.AutoComplete = true;
            LoadBankAccount();
            this.bankAccount.Properties.AutoComplete = true;
        }

        ///////////////////////////////////////////////////////////
        /// Create quotation ID
        ///////////////////////////////////////////////////////////
        private void InitialQuotationID()
        {
            if (this.customerID.SelectedIndex >= 0)
            {
                SqlCommand sql_cmd;
                SqlDataReader dataReader;

                string customerCode = null;                
                string yearmonth = null;
                string indexnumber = null;
                string versionnumber = null;

                ComboboxItem customer = (ComboboxItem)this.customerID.SelectedItem;
                try
                {
                    myConnection.Open();
                    sql_cmd = myConnection.CreateCommand();
                    sql_cmd.CommandText = "SELECT QuotationCode FROM [222_Customer]"
                        + " WHERE CustomerID='" + customer.Value.ToString() + "'";
                    dataReader = sql_cmd.ExecuteReader();
                    if (dataReader.Read())
                    {
                        customerCode = dataReader["QuotationCode"].ToString().Trim();
                    }
                    dataReader.Close();
                    sql_cmd.Dispose();
                    myConnection.Close();
                }
                catch
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                }
                DateTime today = DateTime.Now;
                yearmonth = today.Year.ToString().Substring(2) + today.Month.ToString("D2");

                try
                {
                    myConnection.Open();
                    sql_cmd = myConnection.CreateCommand();
                    sql_cmd.CommandText = "SELECT MAX(SUBSTRING(QuotationID, 10, 3))"
                        + " FROM [241_ExportQuotation]"
                        + " WHERE SUBSTRING(QuotationID, 5, 2) = '" + today.Year.ToString().Substring(2) + "'";
                    dataReader = sql_cmd.ExecuteReader();
                    if (dataReader.Read())
                    {
                        if (!string.IsNullOrEmpty(dataReader[0].ToString().Trim()))
                        {
                            indexnumber = (Int32.Parse(dataReader[0].ToString().Trim()) + 1).ToString("D3");
                        }
                        else
                        {
                            indexnumber = "001";
                        }
                    }
                    dataReader.Close();
                    sql_cmd.Dispose();
                    myConnection.Close();
                }
                catch
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                }

                versionnumber = "0";

                this.quotationID.Text = "Q" + customerCode + "-" + yearmonth + "-" + indexnumber + "-" + versionnumber;
            }
        }

        ///////////////////////////////////////////////////////////
        /// Get Customer List
        ///////////////////////////////////////////////////////////
        private void LoadCustomer()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT CustomerID, FullName FROM [222_Customer] ORDER BY FullName";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["FullName"].ToString();
                    item.Value = dataReader["CustomerID"].ToString();
                    this.customerID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Customer list");
            }
        }

        ///////////////////////////////////////////////////////////
        /// Get Staff List
        ///////////////////////////////////////////////////////////
        private void LoadStaff()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT StaffID, StaffName FROM [222_Staff] ORDER BY StaffName";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["StaffName"].ToString().Trim() + "(" + dataReader["StaffID"].ToString().Trim() + ")";
                    item.Value = dataReader["StaffID"].ToString().Trim();
                    this.requestedBy.Properties.Items.Add(item);
                    this.madeBy.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Staff list");
            }
        }

        ///////////////////////////////////////////////////////////
        /// Get Bank Account List
        ///////////////////////////////////////////////////////////
        private void LoadBankAccount()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT BankAccountID FROM [222_BankAccount] ORDER BY BankAccountID";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["BankAccountID"].ToString();
                    item.Value = dataReader["BankAccountID"].ToString();
                    this.bankAccount.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Bank Account list");
            }
        }

        /////////////////////////////////////
        /// Check new quotation ID conflict
        /////////////////////////////////////
        private bool checkConlict(string id)
        {
            bool ret = false;
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT QuotationID FROM [241_ExportQuotation] WHERE QuotationID='" + id + "'";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    ret = true;
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot check conflict for new quotation ID");
            }
            return ret;
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        ///////////////////////////////////////
        /// Add new export quotation
        ///////////////////////////////////////
        private void buttonAdd_Click(object sender, EventArgs e)
        {
            ComboboxItem item;
            string customer = null, requested = null, made = null, bankinfo = null;            
            bool pass = true;            
            string error = null;

            string quotation_id = null;

            /// Check Quotation ID
            if (string.IsNullOrEmpty(quotationID.Text))
            {
                pass = false;
                error = "No Quotation ID";
            }
            else
            {
                quotation_id = this.quotationID.Text;
            }

            if (pass)
            {
                if (!string.IsNullOrEmpty(this.suffixID.Text))
                {
                    quotation_id += "-" + this.suffixID.Text;
                }
            }

            if (pass)
            {
                if (checkConlict(quotation_id))
                {
                    pass = false;
                    error = "The Quotation ID is existed. Please try again";
                    InitialQuotationID();
                }
            }

            /// Check Input date
            if (pass)
            {
                if (string.IsNullOrEmpty(inputDate.Text))
                {
                    pass = false;
                    error = "Please select Input Date";
                }
            }

            if (pass)
            {
                if (this.requestedBy.SelectedIndex >= 0)
                {
                    item = (ComboboxItem)requestedBy.SelectedItem;
                    requested = item.Value.ToString();
                }

                if (this.madeBy.SelectedIndex >= 0)
                {
                    item = (ComboboxItem)madeBy.SelectedItem;
                    made = item.Value.ToString();
                }
            }

            /// Check Customer
            if (pass)
            {
                if (this.customerID.SelectedIndex < 0)
                {
                    pass = false;
                    error = "Please select Customer";
                }
                else
                {
                    item = (ComboboxItem)customerID.SelectedItem;
                    customer = item.Value.ToString();
                }
            }
                        

            /// Check Bank Info
            if (pass)
            {
                if (this.bankAccount.SelectedIndex < 0)
                {
                    pass = false;
                    error = "Please select Bank Account";
                }
                else
                {
                    item = (ComboboxItem)bankAccount.SelectedItem;
                    bankinfo = item.Value.ToString();
                }
            }

            /// Check Expired date
            if (pass)
            {
                if (string.IsNullOrEmpty(expiredDate.Text))
                {
                    pass = false;
                    error = "Please select Expired Date";
                }
            }

            if (pass)
            {
                /// Insert new Quotation
                DateTime inputdate;
                DateTime.TryParse(inputDate.DateTime.Date.ToString(), out inputdate);
                DateTime expireddate;
                DateTime.TryParse(expiredDate.DateTime.Date.ToString(), out expireddate);
                int comOrpart;
                if (compQuotation.Checked)
                {
                    comOrpart = 1;
                }
                else
                {
                    comOrpart = 0;
                }

                try
                {
                    myConnection.Open();
                    string sql = "INSERT INTO [241_ExportQuotation]"
                        + " (QuotationID, CustomerID, InputDate, RequestedBy, MadeBy, ComOrPart,"
                        + " ExpiredDate, BankInfor, Note)"
                        + " VALUES ('" + quotation_id + "', '" + customer + "', @inputdate, '" + requested + "',"
                        + " '" + made + "', " + comOrpart + ","
                        + " @expireddate, '" + bankinfo + "', '" + noteValue.Text + "')";
                    SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                    cmd_sql.Parameters.Add("@inputdate", SqlDbType.DateTime).Value = inputdate.Date;
                    cmd_sql.Parameters.Add("@expireddate", SqlDbType.DateTime).Value = expireddate.Date;
                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.Dispose();
                    myConnection.Close();
                    MessageBox.Show("Export Quotation added success.");
                    clearForm();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    //MessageBox.Show("Cannot add Export Quotation. Please try again.");
                    MessageBox.Show(ex.Message);
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(error))
                {
                    MessageBox.Show(error);
                }
            }

        }

        private void clearForm()
        {
            foreach (Control c in this.Controls)
            {
                if (c is DevExpress.XtraEditors.TextEdit)
                {
                    ((DevExpress.XtraEditors.TextEdit)c).Text = null;
                }

                if (c is DevExpress.XtraEditors.ComboBoxEdit)
                {
                    ((DevExpress.XtraEditors.ComboBoxEdit)c).SelectedIndex = -1;
                }

                if (c is System.Windows.Forms.MaskedTextBox)
                {
                    ((System.Windows.Forms.MaskedTextBox)c).Text = null;
                }
            }
        }

        private void customerID_SelectedIndexChanged(object sender, EventArgs e)
        {
            InitialQuotationID();
        }
    }
}
